﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;
using System.Configuration;
using System.Collections.Specialized;
using EnterpriseManagement;
using System.Data.SqlClient;
using System.Xml;
using System.Xml.Linq;
using System.Text.RegularExpressions;
using System.Transactions;
using System.Linq.Expressions;
using System.Data;

namespace EnterpriseManagement.Models
{

    #region Models
    /// <summary>
    /// 项目详细信息
    /// </summary>
    public class ProjectDetailsModel
    {
        [DisplayName("项目名称")]
        [Required]
        public string ProjectName { get; set; }

        [DisplayName ("项目类型")]
        [Required]
        public string ProjectType { get; set; }

        [DisplayName ("项目重要程度")]
        [Required]
        public string Importance { get; set; }

        [DisplayName("主要技术")]
        [Required]
        public string MainTechnology { get; set; }

        [DisplayName("项目开始时间")]
        [Required]
        [ValidateDate]
        public string StartTime { get; set; }

        [DisplayName("项目结束时间")]
        [ValidateDate]
        public string EndTime { get; set; }

        [DisplayName("业务领域")]
        public string BusinessField { get; set; }

        [DisplayName("客户")]
        [Required]
        public string ClientName { get; set; }

        [DisplayName("所属部门")]
        [Required]
        public string Department { get; set; }

        [DisplayName("项目经理")]
        public string Manager { get; set; }

        [Required]
        [DisplayName("项目经理ID")]
        public string ManagerId{get;set;}

        [DisplayName("项目人数")]
        public int NumberOfPeople { get; set; }

        [DisplayName("项目状态")]
        [Required]
        public string ProjectStatus { get; set; }

        public void Update(ProjectDetailsModel model)
        {
            this.ProjectName = model.ProjectName;
            this.ProjectType = model.ProjectType;
            this.Importance = model.Importance;
            this.MainTechnology = model.MainTechnology;
            this.StartTime = model.StartTime;
            this.EndTime = model.EndTime;
            this.BusinessField = model.BusinessField;
            this.ClientName = model.ClientName;
            this.Department = model.Department;
            this.Manager = model.Manager;
            this.NumberOfPeople = model.NumberOfPeople;
            this.ProjectStatus = model.ProjectStatus;
            //hidden
            this.ManagerId =model.ManagerId;
        }

    }

    /// <summary>
    /// 项目成员信息
    /// </summary>
    public class TeamMemberModel
    {
        [DisplayName ("项目名称")]
        [Required]
        public string ProjectName { get; set; }

        [Required]
        [DisplayName("员工ID")]
        public string EmployeeId { get; set; }

        [DisplayName("员工姓名")]
        public string EmployeeName { get; set; }

        [DisplayName ("职位")]
        public string Position { get; set; }

        [DisplayName("负责人")]
        public string Director { get; set; }

        [DisplayName ("负责人ID")]
        public string DirectorId { get; set; }

        [DisplayName("开始时间")]
        [ValidateDate]
        public string StartTime { get; set; }

        [DisplayName("结束时间")]
        public string EndTime { get; set; }

        //从项目a释放员工e到项目b时，用于记录老项目
        public string oldProjectName { get; set; }
    }

    /// <summary>
    /// 需改进，在不知道有多少种职位的情况下如何建模？？？？？？？？
    /// </summary>
    public class ProjectEmployeePositionDistributeModel
    {
        [DisplayName ("项目名称")]
        public string ProjectName { get; set; }

        [DisplayName("项目经理")]
        public string Manager { get; set; }

        [DisplayName("开发")]
        public string Dev { get; set; }

        [DisplayName("测试")]
        public string QA { get; set; }

        [DisplayName("业务分析师")]
        public string BA { get; set; }
    }

    public class CreateProjectModel
    {
        [DisplayName("项目名称")]
        public string ProjectName { get; set; }

        [DisplayName("项目经理ID")]
        public string ManagerId { get; set; }

        [DisplayName("项目开始时间(不填则为当前时间)")]
        [ValidateDate]
        public string StartTime { get; set; }
    }

    /// <summary>
    /// 不可更改项目名称，建错项目名称时用到此model来删除建错的项目
    /// </summary>
    public class DeleteProjectModel
    {
        [DisplayName ("项目名称")]
        public string ProjectName { get; set; }

        [DisplayName("超级密码")]
        public string SuperPassword { get; set; }
    }

    public class SearchProjectModel
    {
        [DisplayName("项目名称")]
        public string ProjectName { get; set; }

        [DisplayName("项目类型")]
        public string ProjectType { get; set; }

        [DisplayName("项目重要程度")]
        public string Importance { get; set; }

        [DisplayName("主要技术")]
        public string MainTechnology { get; set; }

        [DisplayName("业务领域")]
        public string BusinessField { get; set; }

        [DisplayName("客户")]
        public string ClientName { get; set; }

        [DisplayName("所属部门")]
        public string Department { get; set; }

        [DisplayName("项目经理（ID）")]
        public string ManagerId { get; set; }

        [DisplayName("项目状态")]
        public string ProjectStatus { get; set; }
    }

    public class CreateClientModel
    {
        [Required]
        [DisplayName ("客户名称")]
        [ValidateClientName ]
        public string ClientName { get; set; }

        [Required]
        [DisplayName("客户地址")]
        public string ClientAddress { get; set; }

        [Required]
        [DisplayName("客户电话")]
        public string ClientPhone { get; set; }
    }


    #endregion

    #region DbHelper
    /// <summary>
    /// SaveChanges本身就是一个transaction
    /// </summary>
    public partial class DbHelper
    {
        public static List<string> GetAllClient()
        {
            using (ProjectEntities currentContext=new ProjectEntities ())
            {
                var clients=from c in currentContext .Clients 
                            select c.client_name;
                return clients .ToList ();
            }
        }

        
        public static ExecuteResult CreateClient(CreateClientModel model)
        {
            Client newClient = new Client();
            newClient.client_name = model.ClientName;
            newClient.client_address = model.ClientAddress;
            newClient.client_phone = model.ClientPhone;
            using (ProjectEntities currentContext = new ProjectEntities())
            {
                try
                {
                    currentContext.Clients.AddObject(newClient);
                    currentContext.SaveChanges();
                }
                catch (Exception ex)
                {
                    return ExecuteResult.CreateClientError;
                }
                return ExecuteResult.Success;
            }
 
        }
        /// <summary>
        /// 获取项目成员信息
        /// </summary>
        /// <param name="projectName"></param>
        /// <param name="employeeId"></param>
        /// <returns></returns>
        public static TeamMemberModel GetTeamMember(string projectName, string employeeId)
        {
            using (ProjectEntities currentContext = new ProjectEntities())
            {
                var tm = from t in currentContext.TeamMembers
                         where t.project_name.Trim() == projectName
                         where t.employee_id == employeeId
                         select t;
                if (tm.Count() != 1)
                    return null;
                TeamMemberModel model = tm.First().ChangeToTeamMemberModel ();
                return model;
            }
        }

        /// <summary>
        /// 获取项目详细信息
        /// </summary>
        /// <param name="projectName"></param>
        /// <param name="currentContext"></param>
        /// <returns></returns>
        public static ProjectDetail GetProjectByProjectName(string projectName, ProjectEntities currentContext=null)
        {
            if (currentContext == null)               //非事务
            {
                using (currentContext = new ProjectEntities())
                {
                    var projects = from p in currentContext.ProjectDetails
                                   where p.project_name == projectName
                                   select p;
                    if (projects.Count() == 0)
                    {
                        return null;
                    }
                    return projects.First();
                }
            }
            else
            {
                var projects = from p in currentContext.ProjectDetails
                               where p.project_name == projectName
                               select p;
                if (projects.Count() == 0)
                {
                    return null;
                }
                return projects.First();
            }
        }

        /// <summary>
        /// 获取用户当前所属项目组信息
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public static List<TeamMember> GetTeamByUserName(string userName, ProjectEntities currentContext = null)
        {
            if (currentContext == null)
            {
                using (currentContext = new ProjectEntities())
                {
                    Employee e = GetEmployeeByUserName(userName);
                    //存在相应的employee
                    if (e != null)
                    {
                        var teams = from t in currentContext.TeamMembers
                                    where t.employee_id == e.employee_id
                                    where string.IsNullOrEmpty(t.end_time)
                                    select t;

                        return teams.ToList();
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            else
            {
                Employee e = GetEmployeeByUserName(userName);
                //存在相应的employee
                if (e != null)
                {
                    var teams = from t in currentContext.TeamMembers
                                where t.employee_id == e.employee_id
                                where string.IsNullOrEmpty(t.end_time)
                                select t;

                    return teams.ToList();
                }
                else
                {
                    return null;
                }
            }
        }
        

        /// <summary>
        /// 删除员工相关项目信息，置endtime为当前时间即可；不填项目名则所有员工相关项目endtime置为当前时间，表示员工离职
        /// 该方法中已包含更新项目组人数，调用之后无需再调用更新项目组人数方法，最好分离成两个方法，事务和非事务
        /// </summary>
        /// <param name="employeeId"></param>
        /// <param name="projectName"></param>
        /// <returns></returns>
        public static ExecuteResult ReleaseTeamMember(string employeeId, string projectName = null, ProjectEntities currentContext = null)
        {
            ExecuteResult result = ExecuteResult.Success;
            ProjectEntities originalContext = currentContext;
            if (currentContext == null)
            {
                currentContext = new ProjectEntities();
            }
            IQueryable<TeamMember> query = from t in currentContext.TeamMembers
                                           where t.employee_id == employeeId
                                           where string.IsNullOrEmpty (t.end_time) 
                                           select t;
            if (!string.IsNullOrEmpty(projectName))
            query = query.Where(t => t.project_name == projectName);

            try
            {
                foreach (TeamMember tm in query)
                {
                    tm.end_time = GetFormatNowDate();
                    ExecuteResult updateresult=DbHelper.UpdateProjectPeopleNumber(tm.project_name,currentContext);
                    result = (updateresult == ExecuteResult.Success ? ExecuteResult.Success : updateresult);
                }
                if (originalContext == null)      //非事务
                {
                    currentContext.SaveChanges();
                    currentContext.Dispose();
                }
                //else                                   //若为事务，最后savechanges就可以了
                //{
                //    currentContext.SaveChanges(false);
                //}
            }
            catch (Exception ex)
            {
                return ExecuteResult.UpdateTeamMemberError;
            }
            return result;
        }

        /// <summary>
        /// 获取当前时间
        /// </summary>
        /// <returns></returns>
        public static string GetFormatNowDate()
        {
            string date;
            string month;
            string year = DateTime.Now.Year.ToString();
            int m = DateTime.Now.Month;
            month = m > 9 ? m.ToString() : "0" + m.ToString();
            int d = DateTime.Now.Day;
            date = d > 9 ? d.ToString() : "0" + d.ToString();

            return year + month + date;
        }

        /// <summary>
        /// 获取所有职位
        /// </summary>
        /// <returns></returns>
        public static List<string> GetAllPositions()
        {
            using (ProjectEntities currentContext = new ProjectEntities())
            {
                var positions = from p in currentContext.Positions
                                select p.position1;
                return positions.ToList();
            }
        }

        /// <summary>
        /// 获取所有项目状态（查找可用，只能获取当前项目中所有项目状态）
        /// </summary>
        /// <returns></returns>
        public static List<string> GetAllProjectStatus()
        {
            using (ProjectEntities currentContext = new ProjectEntities())
            {
                var statuss = (from s in currentContext.ProjectDetails
                               select s.project_status).Distinct();
                return statuss.ToList();
            }

        }

        /// <summary>
        /// 通过projectName获取项目成员信息
        /// </summary>
        /// <param name="projectName"></param>
        /// <returns></returns>
        public static List<TeamMember> GetTeamMembersByProjectName(string projectName)
        {
            using (ProjectEntities currentContext = new ProjectEntities())
            {
                var members = from teammember in currentContext.TeamMembers
                              where projectName == teammember.project_name
                              where string.IsNullOrEmpty(teammember.end_time)
                              select teammember;
                if (members.Count() == 0)
                { return null; }

                return members.ToList();
            }
        }
        
        /// <summary>
        /// 项目人员调度，包含设置员工项目结束时间，新老项目组人数的更新，增加新项目组teammember
        /// </summary>
        /// <param name="employeeId"></param>
        /// <param name="oldProjectName"></param>
        /// <param name="newProjectName"></param>
        /// <param name="position"></param>
        /// <param name="directorId"></param>
        /// <returns></returns>
        public static ExecuteResult DispatchTeammember(string employeeId, string oldProjectName, string newProjectName = "FreeBuffer", string position = "", string directorId = "")
        {
            if (newProjectName == "FreeBuffer")
            {
                //若员工在职项目组大于1，且新项目组是FreeBuffer，则不在Buffer中添加记录
                if (DbHelper.GetTeamByUserName(DbHelper.GetUserById(employeeId).user_name).Count() > 1)
                {
                    return ReleaseTeamMember(employeeId, oldProjectName);
                }
            }

            ProjectEntities currentContext = new ProjectEntities();
            try
            {
                ExecuteResult SetEndtimeResult = SetEndtime(employeeId, oldProjectName,currentContext);
                ExecuteResult UpdateProjectPeopleNumberResult = UpdateProjectPeopleNumber(oldProjectName, newProjectName,currentContext);
                if (SetEndtimeResult != ExecuteResult.Success)
                    return SetEndtimeResult;
                if (UpdateProjectPeopleNumberResult != ExecuteResult.Success)
                    return UpdateProjectPeopleNumberResult;

                
                //若要释放的项目组里面没有该员工的记录，则添加，否则newProject中的teammember记录不变
                //比如说员工A属于项目b,c，  bc先后释放，若此处不加if会造成c释放失败，因为已有相同主键
                if (DbHelper.GetTeamMember(employeeId, newProjectName) == null)
                {
                    TeamMember tm = new TeamMember();
                    tm.employee_id = employeeId;
                    tm.start_time = FormsService.GetFormatNowDate();
                    tm.project_name = newProjectName;
                    tm.position = (string.IsNullOrEmpty (position) ? DbHelper.GetTeamMember(oldProjectName, employeeId).Position : position);
                    tm.director_id = (string.IsNullOrEmpty(directorId) ? DbHelper.GetProjectByProjectName(newProjectName).manager_id : directorId);
                    currentContext.TeamMembers.AddObject(tm);
                }
                //SaveOptions.DetectChangesBeforeSave        Accept之后才保存到数据源
                currentContext.SaveChanges();

            }
            catch (Exception ex)
            {
                currentContext.Dispose();
                return ExecuteResult.ChangeProjectError;
            }
            
            currentContext.Dispose();
            return ExecuteResult.Success;
        }

        /// <summary>
        /// 当一个员工从项目组A到项目组B时，调用此函数更新项目人员信息，一般不单独调用；
        /// </summary>
        /// <param name="oldProjectName"></param>
        /// <param name="newProjectName"></param>
        /// <param name="context"></param>
        /// <returns></returns>
        private static ExecuteResult UpdateProjectPeopleNumber(string oldProjectName, string newProjectName,ProjectEntities context=null )
        {
            ProjectEntities originalContext = context;
            if (context == null)
                context = new ProjectEntities();
            
            var oldProject = from p in context.ProjectDetails
                             where oldProjectName == p.project_name
                             select p;
            var newProject = from p in context.ProjectDetails
                             where newProjectName == p.project_name
                             select p;
            if (oldProject.Count() == 0 || newProjectName.Count() == 0)
            { return ExecuteResult.UpdateProjectPeopleNumberError; }

            try
            {
                oldProject.First().number_of_people -= 1;
                newProject.First().number_of_people += 1;
                if (originalContext == null)                    //非事务
                {
                    context.SaveChanges();
                    context.Dispose();
                }
            }
            catch (Exception ex)
            {
                context.Dispose();
                return ExecuteResult.UpdateProjectPeopleNumberError;
            }

            return ExecuteResult.Success;
        }

        /// <summary>
        /// 员工离职时更新项目组人员数量
        /// </summary>
        /// <param name="projectName"></param>
        /// <returns></returns>
        public static ExecuteResult UpdateProjectPeopleNumber(string projectName,ProjectEntities context=null)
        {
            ProjectEntities originalContext = context;
            if (context == null)
                context = new ProjectEntities();
            var project = from p in context.ProjectDetails
                          where projectName == p.project_name.Trim()
                          where string.IsNullOrEmpty(p.endtime)
                          select p;
            if (project.Count() == 0)
            { return ExecuteResult.UpdateProjectPeopleNumberError; }

            try
            {
                project.First().number_of_people -= 1;
                if (originalContext == null)                //非事务
                {
                    context.SaveChanges();
                    context.Dispose();
                }
                //else
                //{ context.SaveChanges(false); }
            }
            catch (Exception ex)
            {
                context.Dispose();
                return ExecuteResult.UpdateProjectPeopleNumberError;
            }

            return ExecuteResult.Success;
        }

        //Leave and change project
        /// <summary>
        /// 设置结束时间，默认设置为当前时间
        /// </summary>
        /// <param name="employeeId"></param>
        /// <param name="projectName"></param>
        /// <returns></returns>
        public static ExecuteResult SetEndtime(string employeeId, string projectName,ProjectEntities context=null, string endtime = null)
        {
            ProjectEntities originalContext = context;
            if (context == null)
                context = new ProjectEntities();
            //查找相关的员工的项目组信息，若要设置Endtime的员工endtime不为Null,则报错
            var employeeTeam = from t in context.TeamMembers
                                where t.employee_id == employeeId 
                                where t.project_name == projectName 
                                where t.end_time ==null
                                select t;
            if (employeeTeam.Count() == 0)
            { return ExecuteResult.SetEndTimeError; }

            if (endtime == null)
            { endtime = FormsService.GetFormatNowDate(); }

            try
            {
                employeeTeam.First().end_time = endtime;
                if (originalContext == null)                       //非事务
                {
                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {
                return ExecuteResult.SetEndTimeError;
            }
            return ExecuteResult.Success; 
        }

        /// <summary>
        /// 获取项目的项目名称
        /// </summary>
        /// <param name="includeFinishedProject"></param>
        /// <returns></returns>
        public static List<string> GetAllProjectNames(bool includeFinishedProject = false)
        {
            using (ProjectEntities currentContext = new ProjectEntities())
            {
                //如果需要包含历史信息
                if (includeFinishedProject)
                {
                    var allProjects = from p in currentContext.ProjectDetails
                                   select p.project_name;
                    return allProjects.ToList();
                }
                //若只显示目前项目信息
                var currentProjects = from p in currentContext.ProjectDetails
                               where string.IsNullOrEmpty (p.endtime)
                               select p.project_name;
                return currentProjects.ToList();
            }
        }

        /// <summary>
        /// 更新项目信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static ExecuteResult UpdateProject(ProjectDetailsModel model)
        {
            using (ProjectEntities currentContext = new ProjectEntities())
            {
                var projects = from p in currentContext.ProjectDetails
                               where p.project_name == model.ProjectName
                               select p;
                if (projects.Count() == 0)
                    return ExecuteResult.UpdateProjectError;
                ProjectDetail project = projects.First();
                try
                {
                    project.Update(model);
                    currentContext.SaveChanges();
                }
                catch (Exception ex)
                {
                    return ExecuteResult.UpdateProjectError;
                }
                return ExecuteResult.Success;
            }
        }

        /// <summary>
        /// 创建项目；
        /// </summary>
        /// <param name="projectName"></param>
        /// <param name="managerId"></param>
        /// <param name="starttime"></param>
        /// <returns></returns>
        public static ExecuteResult CreateProject(string projectName, string managerId, string starttime,ProjectEntities context=null)
        {
            ProjectEntities originalContex = context;
            if (context == null)
            {
                context = new ProjectEntities();
            }
            try
            {
                ProjectDetail newProject = new ProjectDetail(projectName, managerId, starttime);
                context.ProjectDetails.AddObject(newProject);
                if (originalContex == null)            //非事务
                {
                    context.SaveChanges(); 
                    context.Dispose();
                }
            }
            catch (Exception ex)
            {
                context.Dispose();
                return ExecuteResult.CreateProjectError;
            }

            return ExecuteResult.Success;
        }

        /// <summary>
        /// 只有在新建项目出错时程序内部调用(super)
        /// </summary>
        /// <param name="projectName"></param>
        /// <returns></returns>
        public static ExecuteResult DeleteProject(string projectName)
        {
            ProjectEntities currentContext = new ProjectEntities();
            try
            {
                var projects = from p in currentContext.ProjectDetails
                               where p.project_name == projectName
                               select p;
                if (projects.Count() == 0)
                    return ExecuteResult.DeleteProjectError;
                currentContext.ProjectDetails.DeleteObject(projects.First());
                currentContext.SaveChanges();
            }
            catch (Exception ex)
            {
                currentContext.Dispose();
                return ExecuteResult.DeleteProjectError;
            }
            currentContext.Dispose();
            return ExecuteResult.Success;
        }

        /// <summary>
        /// 获取按职位分布列表，暂时！
        /// </summary>
        public static List<ProjectEmployeePositionDistributeModel> GetEmployeeDistributeByPosition()
        {
            List<ProjectEmployeePositionDistributeModel> modelList = new List<ProjectEmployeePositionDistributeModel>();
            using (ProjectEntities currentContext = new ProjectEntities())
            {
                var list = from tm in currentContext.TeamMembers
                           where string.IsNullOrEmpty(tm.end_time)
                           group tm by tm.project_name into p
                           select new
                           {
                               ProjectName = p.Key,
                               Dev = p.Count(tm => tm.position == "Dev"),
                               QA = p.Count(tm => tm.position == "QA"),
                               BA = p.Count(tm => tm.position == "BA")
                           };
              
                foreach(var l in list)
                {
                    ProjectEmployeePositionDistributeModel model = new ProjectEmployeePositionDistributeModel();
                    model.ProjectName = l.ProjectName;
                    model.Manager =GetEmployeeById ( GetProjectByProjectName(l.ProjectName).manager_id).name;
                    model.Dev = l.Dev.ToString ();
                    model.QA = l.QA.ToString();
                    model.BA = l.BA.ToString();
                    modelList.Add(model);
                }
            }
            return modelList;
           
        }

        ///// <summary>
        ///// 获取历史项目信息，只要判断endtime是否为null即可
        ///// </summary>
        ///// <returns>有可能list.count()==0</returns>
        //public static List<ProjectDetail > GetHistoryProject()
        //{
        //    using (ProjectEntities currentContext = new ProjectEntities())
        //    {
        //        var projects = from p in currentContext.ProjectDetails
        //                       where p.endtime !=null 
        //                       select p;
        //        return projects.ToList();
        //    }
        //}
       
        /// <summary>
        /// 条件查询项目组
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static List<ProjectDetail> SearchProject(SearchProjectModel model)
        {
            using (ProjectEntities currentContext = new ProjectEntities())
            {
                var query = from p in currentContext.ProjectDetails
                            select p;

                if (!string.IsNullOrEmpty(model.ProjectName))
                    query = query.Where(p => p.project_name == model.ProjectName);

                if (!string.IsNullOrEmpty(model.ProjectType))
                    query = query.Where(p => p.project_type == model.ProjectType);

                if (!string.IsNullOrEmpty(model.Importance))
                    query = query.Where(p => p.importance == model.Importance);

                if (!string.IsNullOrEmpty(model.MainTechnology))
                    query = query.Where(p => p.main_technology == model.MainTechnology);

                if (!string.IsNullOrEmpty(model.BusinessField))
                    query = query.Where(p => p.business_field == model.BusinessField);

                if (!string.IsNullOrEmpty(model.ClientName))
                    query = query.Where(p => p.client_name == model.ClientName);

                if (!string.IsNullOrEmpty(model.ManagerId))
                    query = query.Where(p => p.manager_id == model.ManagerId);

                if (!string.IsNullOrEmpty(model.Department))
                    query = query.Where(p => p.department == model.Department);

                if (!string.IsNullOrEmpty(model.ProjectStatus))
                    query = query.Where(p => p.project_status == model.ProjectStatus);

                return query.ToList();
            }
        }

        //private static bool AllFieldsEmpty(string projectName = "", string projectType = "", string Importance = "",
        //                                          string mainTechnology = "", string businessField = "", string client = "",
        //                                          string department = "", string manager = "", string projectStatus = "")
        //{
        //    return projectName ==string.Empty 
        //}

        /// <summary>
        /// 按客户分布
        /// </summary>
        /// <returns></returns>
        public static Dictionary <string ,List<ProjectDetail>> GetProjectDistributeByClient()
        {
            using (ProjectEntities currentContext = new ProjectEntities())
            {
                Dictionary <string ,List<ProjectDetail>> dictionary=new Dictionary<string,List<ProjectDetail>>();
                var projects=from p in currentContext .ProjectDetails 
                             select p;

                foreach (ProjectDetail pd in projects)
                {
                    if (!dictionary.ContainsKey(pd.client_name))
                    {
                        dictionary.Add(pd.client_name, new List<ProjectDetail>());
                    }
                    dictionary[pd.client_name].Add(pd);
                }
                return dictionary;
            }
        }

        /// <summary>
        /// 业务领域分布
        /// </summary>
        /// <returns></returns>
        public static Dictionary<string, List<ProjectDetail>> GetProjectDistributeByBusinessField()
        {
            using (ProjectEntities currentContext = new ProjectEntities())
            {
                Dictionary<string, List<ProjectDetail>> dictionary = new Dictionary<string, List<ProjectDetail>>();
                var projects = from p in currentContext.ProjectDetails
                               select p;

                foreach (ProjectDetail pd in projects)
                {
                    if (!dictionary.ContainsKey(pd.business_field))
                    {
                        dictionary.Add(pd.business_field, new List<ProjectDetail>());
                    }
                    dictionary[pd.business_field].Add(pd);
                }
                return dictionary;
            }
        }

        /// <summary>
        /// 添加项目成员，一个人可以同属多个项目组，项目A的员工可以被添加到项目B（比如说PM），但是在Buffer中的员工除外
        /// </summary>
        /// <param name="tm"></param>
        /// <returns></returns>
        public static ExecuteResult AddTeammember(string projectName,string employeeId,string position="",string starttime=null,string directorId=null,ProjectEntities currentContext=null)
        {
            //如果输入的员工已经离职
            Employee e=GetEmployeeById(employeeId);
            if (e!=null&&!string.IsNullOrEmpty (e.leave_date))
            {
                return ExecuteResult.AddTeammemberError;
            }

            ProjectEntities originalContext = currentContext;
            if (originalContext == null)
            {
                currentContext = new ProjectEntities();
            }
            //判断buffer中是否有该员工在职记录，有则置endtime为Now;
            var buffertm = from t in currentContext.TeamMembers
                           where t.project_name == "FreeBuffer"
                           where t.end_time != null
                           where t.employee_id == employeeId
                           select t;
            try
            {
                if (buffertm.Count() != 0)
                {
                    buffertm.First().end_time = GetFormatNowDate();
                    GetProjectByProjectName("FreeBuffer").number_of_people -= 1;
                }
                GetProjectByProjectName(projectName,currentContext).number_of_people += 1;
                TeamMember tm = new TeamMember(projectName, employeeId, position, directorId, starttime);
                currentContext.TeamMembers.AddObject(tm);
                if (originalContext == null)                //非事务
                {
                    currentContext.SaveChanges();
                }
            }
            catch (Exception ex)
            {
                return ExecuteResult.AddTeammemberError;
            }
            return ExecuteResult.Success;
            
        }

        /// <summary>
        /// 更新项目成员信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static ExecuteResult UpdateTeammember(TeamMemberModel model)
        {
            using (ProjectEntities currentContext = new ProjectEntities())
            {
                var tm = from t in currentContext.TeamMembers
                         where t.employee_id == model.EmployeeId
                         where t.project_name.Trim() == model.ProjectName.Trim()
                         select t;
                if (tm.Count() != 1)
                    return ExecuteResult.UpdateTeamMemberError;
                try
                {
                    tm.First().UpdateTeammember(model);
                    currentContext.SaveChanges();
                    currentContext.Dispose();
                }
                catch (Exception ex)
                {
                    currentContext.Dispose();
                    return ExecuteResult.UpdateTeamMemberError;
                }
                return ExecuteResult.Success;
            }
        }
    
    }

    #endregion

    #region Service

    public partial class FormsService
    {
        public static List<string> GetAllClient()
        {
            return DbHelper.GetAllClient();
        }

        public static ExecuteResult CreateClient(CreateClientModel model)
        {
            return DbHelper.CreateClient(model);
        }

        public static ExecuteResult UpdateTeammember(TeamMemberModel model)
        {
            return DbHelper.UpdateTeammember(model);
            
        }

        public static ProjectDetailsModel GetProjectByProjectName(string projectName)
        {
            ProjectDetail project = DbHelper.GetProjectByProjectName(projectName);
            if (project == null)
                return null;
            return project.ChangetoProjectDetailsModel();
        }

        public static List<ProjectDetailsModel> SearchProject(SearchProjectModel model)
        {
            //data validation
            List<ProjectDetail> projectList=DbHelper.SearchProject(model);
            List<ProjectDetailsModel> list = new List<ProjectDetailsModel>();
            foreach (ProjectDetail p in projectList)
            {
                list.Add(p.ChangetoProjectDetailsModel());
            }
            return list;
        }
       
        /// <summary>
        /// 更新项目信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static ExecuteResult UpdateProject(ProjectDetailsModel model)
        {
            //data validation
            return DbHelper.UpdateProject(model);
        }

        public static ExecuteResult AddTeammember(string projectName, string employeeId, string position = "PM", string starttime = null, string directorId = null)
        {
            //data validation
            return DbHelper .AddTeammember (projectName,employeeId,position,starttime,directorId);
        }

        /// <summary>
        /// 获取所有项目项目名称
        /// </summary>
        /// <param name="includeFinishedProject"></param>
        /// <returns></returns>
        public static List<string> GetAllProjectNames(bool includeFinishedProject=false)
        {
            return DbHelper.GetAllProjectNames(includeFinishedProject);
        }


        //获得格式化后的当前时间
        public static string GetFormatNowDate()
        {
            string date;
            string month;
            string year = DateTime.Now.Year.ToString();
            int m = DateTime.Now.Month;
            month = m > 9 ? m.ToString() : "0" + m.ToString();
            int d = DateTime.Now.Day;
            date = d > 9 ? d.ToString() : "0" + d.ToString();

            return year + month + date;
        }

        /// <summary>
        /// 通过登录名获得该用户的项目信息，存放在一个List里面，若不存在与该员工相关项目信息则返回Null
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <returns>(List) ProjectDetailsModel</returns>
        public static List<ProjectDetailsModel> GetProjectDetail(string userName)
        {
           List<TeamMember> teamList = DbHelper.GetTeamByUserName(userName);
           if (teamList.Count > 0)
           {
               List<ProjectDetailsModel> projectList = new List<ProjectDetailsModel>();
               foreach (TeamMember tm in teamList)
               {
                   ProjectDetail projectDetail = DbHelper.GetProjectByProjectName(tm.project_name);
                   if (projectDetail != null)
                   {
                       ProjectDetailsModel model = new ProjectDetailsModel();
                       model.BusinessField = projectDetail.business_field;
                       model.ClientName = projectDetail.client_name;
                       model.Department = projectDetail.department;
                       model.EndTime = projectDetail.endtime;
                       model.Importance = projectDetail.importance;
                       model.MainTechnology = projectDetail.main_technology;
                       model.Manager = DbHelper.GetEmployeeById(tm.employee_id).name;
                       model.NumberOfPeople = (int)projectDetail.number_of_people;
                       model.ProjectName = projectDetail.project_name;
                       model.ProjectStatus = projectDetail.project_status;
                       model.ProjectType = projectDetail.project_type;
                       model.StartTime = projectDetail.starttime;
                       model.ManagerId = tm.employee_id;

                       projectList.Add(model);
                   }
                   else
                   {
                       return null;
                   }
               }
               return projectList;
           }
           else 
           {
               return null;
           }
        }

        public static List<string> GetAllPositions()
        {
            return DbHelper.GetAllPositions();
        }

        public static List<string> GetAllProjectStatus()
        {
            return DbHelper.GetAllProjectStatus();
        }
        /// <summary>
        /// 通过项目名称获取项目成员信息
        /// </summary>
        /// <param name="projectName"></param>
        /// <returns></returns>
        public static List<TeamMemberModel> GetTeamMembers(string projectName)
        {
            List<TeamMember> teamMembers = DbHelper.GetTeamMembersByProjectName(projectName);
            if (teamMembers == null)
            { return null; }

            List<TeamMemberModel> list = new List<TeamMemberModel>();
            foreach (TeamMember tm in teamMembers)
            {
                TeamMemberModel tmModel = tm.ChangeToTeamMemberModel();
                list.Add(tmModel);
            }

            return list;
        }

        

        public static ExecuteResult DispatchTeammember(string employeeId, string oldProjectName, string newProjectName = "FreeBuffer", string position = "", string directorId = "")
        {
            return DbHelper.DispatchTeammember(employeeId, oldProjectName, newProjectName, position, directorId);
        }

      
        /// <summary>
        /// 超管才能创建项目，指定pm和projectName即可，项目开始时间为默认为项目创建时间，然后其他信息都由PM填写
        /// 创建项目的同时会在teammember表加上一条员工的项目纪录
        /// </summary>
        /// <param name="projectName"></param>
        /// <param name="employeeId"></param>
        /// <param name="starttime"></param>
        /// <returns></returns>
        public static ExecuteResult CreateProject(string projectName, string managerId, string starttime = null)
        {
            using (ProjectEntities currentContext = new ProjectEntities())
            {
                if (starttime == null)
                    starttime = GetFormatNowDate();
                //ExecuteResult result1 = DbHelper.CreateProject(projectName, managerId, starttime,currentContext);
               //先 生成新项目组才能添加项目经理
                ExecuteResult result1 = DbHelper.CreateProject(projectName, managerId, starttime);
                ExecuteResult result2 = DbHelper.AddTeammember(projectName, managerId, "PM", starttime,null,currentContext);
                if (result1 == ExecuteResult.Success )
                {
                    if (result2 == ExecuteResult.Success)
                    {
                        try
                        {
                            currentContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            return ExecuteResult.CreateProjectError;
                        }
                        return ExecuteResult.Success;
                    }
                    else
                    {
                        //添加项目经理失败，回滚，删除新建的项目组；
                        DbHelper.DeleteProject(projectName);
                    }
                }
                return ExecuteResult.CreateProjectError;
            }
        }
        /// <summary>
        /// 改变员工的项目组，默认新项目为FreeBuffer；员工调配时先release到Buffer,然后由Buffer(默认超管才能调)调到相应项目组。
        /// </summary>
        /// <param name="employeeId"></param>
        /// <param name="oldProject"></param>
        /// <param name="newProject"></param>
        /// <returns></returns>
        public static ExecuteResult ChangeMemberProject(string employeeId,string oldProject,string newProject="FreeBuffer")
        {
            //data validation here
            return DbHelper.DispatchTeammember(employeeId, oldProject, newProject);
        }

        /***********************************************************************************************************/
        /********************这些分布表最后可以实现成一个factory，通过特定的标志提取不同的分布表****************************/
        /***********************************************************************************************************/

        /// <summary>
        /// 按职位分布表
        /// </summary>
        /// <returns></returns>
        public static List<ProjectEmployeePositionDistributeModel> GetProjectEmployeePositionDistribute()
        {
            //data validation here
            return DbHelper.GetEmployeeDistributeByPosition();
        }

        /// <summary>
        /// 按客户分布
        /// </summary>
        /// <returns></returns>
        public static Dictionary<string, List<ProjectDetail>> GetProjectDistributeByClient()
        {
            //data validation here
            return DbHelper.GetProjectDistributeByClient();
        }

        /// <summary>
        /// 按业务领域分布
        /// </summary>
        /// <returns></returns>
        public static Dictionary<string, List<ProjectDetail>> GetProjectDistributeByBusinessField()
        {
            //data validation here
            return DbHelper.GetProjectDistributeByBusinessField();
        }

        public static ExecuteResult DeleteProject(string projectName, string superPassword)
        {
            string userName = HttpContext.Current.User.Identity.Name;
            User_Login user;
            if (DbHelper.ValidateUser(userName, superPassword,out user))
            {
               ExecuteResult result= DbHelper.DeleteProject(projectName);
               return result;
            }
            return ExecuteResult.PasswordError;
        }

        public static TeamMemberModel GetTeamMember(string projectName, string employeeId)
        {
            return DbHelper.GetTeamMember(projectName ,employeeId );
        }

    }

    /// <summary>
    /// 验证需创建的客户中是否已存在
    /// </summary>
    public class ValidateClientNameAttribute:ValidationAttribute
    {
        public const string _defaultErrorMessage = "客户名已存在，请更改后重试";

        public ValidateClientNameAttribute() : base(_defaultErrorMessage) { }

        public override bool IsValid(object value)
        {
            string clientName = value as string;
            if (string.IsNullOrEmpty(clientName))
                return false;
            if (ClientNameExist(clientName))
                return false;
            return true;
        }

        private bool ClientNameExist(string clientName)
        { 
            using (ProjectEntities currentContext = new ProjectEntities())
            {
                var client = from c in currentContext.Clients
                             where clientName.Trim() == c.client_name.Trim()
                             select c;
                if (client.Count() > 0)
                    return true;
                return false ;
            }
        }
        
 
    }

    #endregion

}